package Dao.TitleAndPaper.Imp;

import Dao.TitleAndPaper.PaperDao;
import Entity.TitleAndPaper.Paper;
import Util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class PaperDaoImp implements PaperDao {
    private PreparedStatement ps = null;
    private ResultSet rs;


    @Override
    public List<Paper> getAllPaper(String queryKey, Integer offset, Integer limit, String No, Integer type, Connection conn) {
        List<Paper> papers = new ArrayList<>();
        String sql = "";
        if (type == 1) {
            sql = "select p.bh, p.cid, p.sno, p.tno, p.title, p.state, p.bz, p.xt_id ,s.name,p.reply_group " +
                    "from paper p,stu_info s " +
                    "where  p.sno = s.sno and p.title like ? and p.sno like ?  limit ?,?";
        } else if (type == 2) {
            sql = "select p.bh, p.cid, p.sno, p.tno, p.title, p.state, p.bz, p.xt_id ,s.name,p.reply_group " +
                    "from paper p,stu_info s " +
                    "where  p.sno = s.sno and p.title like ? and p.tno like ?  limit ?,?";
        } else {
            sql = "select p.bh, p.cid, p.sno, p.tno, p.title, p.state, p.bz, p.xt_id ,s.name,p.reply_group " +
                    "from paper p,stu_info s " +
                    "where  p.sno = s.sno and p.title like ?  limit ?,?";
        }
        try {
            ps = conn.prepareStatement(sql);
            if (type == 1 || type == 2) {
                ps.setString(1, queryKey);
                ps.setString(2, No);
                ps.setInt(3, offset);
                ps.setInt(4, limit);
            } else {
                ps.setString(1, queryKey);
                ps.setInt(2, offset);
                ps.setInt(3, limit);
            }
            rs = ps.executeQuery();
            while (rs.next()) {
                Integer bh = rs.getInt("p.bh");
                String cid = rs.getString("p.cid");
                String sno = rs.getString("p.sno");
                String tno = rs.getString("p.tno");
                String title = rs.getString("p.title");
                String state = rs.getString("p.state");
                String bz = rs.getString("p.bz");
                Integer xtId = rs.getInt("p.xt_id");
                String sname = rs.getString("s.name");
                String replyGroup = rs.getString("p.reply_group");
                Paper paper = new Paper(bh, cid, sno, tno, title, state, bz, xtId, sname, replyGroup, null, null, null, null, null, null);
                papers.add(paper);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeMySQLResource(ps, rs);
        }
        return papers;
    }


    @Override
    public List<Paper> getAllPaperGroup(Connection conn) {
        List<Paper> papers = new ArrayList<>();
        String sql = "select bh, cid, sno, tno, title, state,bz, xt_id ,reply_group " +
                "from paper " +
                "where  state = '定稿评分'";
        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                Integer bh = rs.getInt("bh");
                String cid = rs.getString("cid");
                String sno = rs.getString("sno");
                String tno = rs.getString("tno");
                String title = rs.getString("title");
                String state = rs.getString("state");
                String bz = rs.getString("bz");
                Integer xtId = rs.getInt("xt_id");
                String replyGroup = rs.getString("reply_group");
                Paper paper = new Paper(bh, cid, sno, tno, title, state, bz, xtId, null, replyGroup, null, null, null, null, null, null);
                papers.add(paper);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeMySQLResource(ps, rs);
        }
        return papers;
    }

    @Override
    public Integer updateCheck(String bh, String state, Connection conn) {
        int res = 0;
        try {
            String sql = "update paper set state = ? where bh = ? ";
            ps = conn.prepareStatement(sql);
            ps.setString(1, state);
            ps.setString(2, bh);
            res = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeMySQLResource(ps, rs);
        }
        return res;
    }


    @Override
    public Integer updateGroup(Paper paper, Connection conn) {
        int res = 0;
        try {
            String sql = "update paper set reply_group = ? where bh = ? ";
            ps = conn.prepareStatement(sql);
            ps.setString(1, paper.getReplyGroup());
            ps.setInt(2, paper.getBh());
            res = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeMySQLResource(ps, rs);
        }
        return res;
    }

    @Override
    public Integer getSumByTNO(String TNO, Connection conn) {
        int res = 0;
        try {
            String sql = "select COUNT(sno) from paper  where tno = ? ";
            ps = conn.prepareStatement(sql);
            ps.setString(1, TNO);
            rs = ps.executeQuery();
            if (rs.next()) {
                res = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeMySQLResource(ps, rs);
        }
        return res;
    }
}
